<?php
App::uses('AppController', 'Controller');
/**
 * Modelportfolios Controller
 *
 * @property Modelportfolio $Modelportfolio
 */
class ModelportfoliosController extends AppController {

	//	$helpers = array('RPM Chart');

/**
 * index method
 *
 * @return void
 */
	public function index() {
		/*cakephp original codes
		$this->Modelportfolio->recursive = 0;
		$this->set('modelportfolios', $this->paginate());
		*/
		        if(!empty($this->request->data)){
            $this->redirect(array('action'=>'rpmperformance', $this->request->data['RPMChartDate']['date2']));
        }

		$con=mysqli_connect("localhost","root","","dfs");
		$sql="SELECT DISTINCT portfolioname From modelportfolios ORDER BY portfolioname";
		$temp=mysqli_query($con,$sql);
		$counter=0;
		foreach ($temp as $temp2)
		{
			foreach($temp2 as $temp3)
			{
				//get portfolio name
				$this->Session->write("portfolioname[".$counter."]",$temp3);
				//get portfolio id
				$sql2="SELECT portfoliosid FROM modelportfolios where portfolioname='".$temp3."' order by date desc limit 1";
				$tempID=mysqli_query($con,$sql2);
				foreach ($tempID as $tID)
				{
					foreach ($tID as $td)
					{
						$this->Session->write("portfoliosid[".$counter."]",$td);
					}
				} 
				$tempID->close();
				$counter ++;
			}

		}
		$this->Session->write("counter",$counter);
		$temp->close();
		$con->close();
	}

/**
 * view method
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */
	public function view($id = null) {
		include("Component/connection.php");
		if (!$this->Modelportfolio->exists($id)) {
			throw new NotFoundException(__('Invalid modelportfolio'));
		}
		$options = array('conditions' => array('Modelportfolio.' . $this->Modelportfolio->primaryKey => $id));
		$this->set('modelportfolio', $this->Modelportfolio->find('first', $options));
		
		//$con=mysqli_connect("localhost","root","","dfs");
		$sql="SELECT portfolioname from modelportfolios where portfoliosid = '".$id."'";
		$temp=mysqli_query($con,$sql);

		foreach($temp as $temp2)
		{
			foreach($temp2 as $temp3)
			{
				$portfolioname=$temp3;
			}
		}

		$sql2="SELECT portfoliosid,date,fundslist,indexlist,OneMonthReturn,ThreeMonthReturn,SixMonthReturn,OneYearReturn,TwoYearReturn,ThreeYearReturn,FourYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='".$portfolioname."' order by date desc limit 10";
		$tempRecord=mysqli_query($con,$sql2);

		$counter=0;
		$counter2=0;
		foreach ($tempRecord as $tempR)
		{
			//debug($tempR);
			$counter2=0;
			foreach($tempR as $tr)
			{
				$this->Session->write("record[".$counter."][".$counter2."]",$tr);
				$counter2++;
			}
			$counter++;
			$this->Session->write("recordPortPer",$counter2);
		}

		$this->Session->write("recordPortTotal",$counter);

		//Fund
		$sql3="SELECT fundslist from modelportfolios where portfoliosid = '".$id."'";
		$tempFlist=mysqli_query($con,$sql3);
		foreach($tempFlist as $temp2)
		{
			foreach($temp2 as $temp3)
			{
				$tempfundlist=$temp3;
			}
		}

		$fundlist=explode(',',$tempfundlist);
		$noOfFundCounter=0;
		$fundcounter1=0;
		foreach($fundlist as $fund)
		{
			$noOfFundCounter++;
			//Funds' id for generating link
			$sqlT2="SELECT fundsid from managedfunds where fundname='".$fund."' order by date desc limit 1";
			$tempRecord2=mysqli_query($con,$sqlT2);
			foreach($tempRecord2 as $tr2)
			{
				foreach($tr2 as $tr3)
				{
					$this->Session->write("FundID[".$noOfFundCounter."]",$tr3);
				}
			}
			//all funds's detail
			$sqlT="SELECT fundsid,fundname,date,OneMonthReturn,ThreeMonthReturn,SixMonthReturn,OneYearReturn,TwoYearReturn,ThreeYearReturn,FourYearReturn,FiveYearReturn,SinceInceptionReturn from managedfunds where fundname='".$fund."' order by date desc limit 5";
			$tempRecord=mysqli_query($con,$sqlT);
			foreach ($tempRecord as $tempR)
			{
				//debug($tempR);
				$fundcounter2=0;
				foreach($tempR as $tr)
				{
					$this->Session->write("fundRecord[".$fundcounter1."][".$fundcounter2."]",$tr);
					$fundcounter2++;
				}
				$fundcounter1++;
				$this->Session->write("recordFundPer",$fundcounter2);
			}
			$this->Session->write("recordFundTotal",$fundcounter1);
		}
		$this->Session->write("noFund",$noOfFundCounter);

		//Index
		$sql4="SELECT indexlist from modelportfolios where portfoliosid = '".$id."'";
		$tempIlist=mysqli_query($con,$sql4);
		foreach($tempIlist as $temp4)
		{
			foreach($temp4 as $temp5)
			{
				$tempIlist=$temp5;
			}
		}

		$indexlist=explode(',',$tempIlist);
		$noOfIndexCounter=0;
		$indexcounter1=0;
		foreach($indexlist as $index)
		{
			$noOfIndexCounter++;
			//ndex id for generating link
			$sqlT3="SELECT indicesid from indices where indexname='".$index."' order by date desc limit 1";
			$tempRecord2=mysqli_query($con,$sqlT);
			foreach($tempRecord2 as $tr2)
			{
				foreach($tr2 as $tr3)
				{
					$this->Session->write("IndexID[".$noOfIndexCounter."]",$tr3);
				}
			}
			//index detail
			$sqlT4="SELECT indicesid,indexname,date,OneMonthReturn,ThreeMonthReturn,SixMonthReturn,OneYearReturn,TwoYearReturn,ThreeYearReturn,FourYearReturn,FiveYearReturn,SinceInceptionReturn from indices where indexname='".$index."' order by date desc limit 5";
			$tempRecord4=mysqli_query($con,$sqlT4);
			foreach ($tempRecord4 as $tempR)
			{
				$indexcounter2=0;
				foreach($tempR as $tr)
				{
					$this->Session->write("IndexRecord[".$indexcounter1."][".$indexcounter2."]",$tr);
					$indexcounter2++;
				}
				$indexcounter1++;
				$this->Session->write("recordIndexPer",$indexcounter2);
			}
			$this->Session->write("recordIndexTotal",$indexcounter1);
		}
		$this->Session->write("noIndex",$noOfIndexCounter);
	}

/**
 * add method
 *
 * @return void
 */
	public function add() {
		if ($this->request->is('post')) {
			$this->Modelportfolio->create();
			if ($this->Modelportfolio->save($this->request->data)) {
				$this->Session->setFlash(__('The modelportfolio has been saved'));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The modelportfolio could not be saved. Please, try again.'));
			}
		}

		$this->loadModel('Portfolioname');
		$portfolios = $this->Portfolioname->find('list');
		$this->set(compact('portfolios'));
	}

/**
 * edit method
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */
	public function edit($id = null) {
		if (!$this->Modelportfolio->exists($id)) {
			throw new NotFoundException(__('Invalid modelportfolio'));
		}
		if ($this->request->is('post') || $this->request->is('put')) {
			if ($this->Modelportfolio->save($this->request->data)) {
				$this->Session->setFlash(__('The modelportfolio has been saved'));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The modelportfolio could not be saved. Please, try again.'));
			}
		} else {
			$options = array('conditions' => array('Modelportfolio.' . $this->Modelportfolio->primaryKey => $id));
			$this->request->data = $this->Modelportfolio->find('first', $options);
		}

		$this->loadModel('Portfolioname');
		$portfolios = $this->Portfolioname->find('list');
		$this->set(compact('portfolios'));
	}

/**
 * delete method
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */
	public function delete($id = null) {
		$this->Modelportfolio->id = $id;
		if (!$this->Modelportfolio->exists()) {
			throw new NotFoundException(__('Invalid modelportfolio'));
		}
		$this->request->onlyAllow('post', 'delete');
		if ($this->Modelportfolio->delete()) {
			$this->Session->setFlash(__('Modelportfolio deleted'));
			$this->redirect(array('action' => 'index'));
		}
		$this->Session->setFlash(__('Modelportfolio was not deleted'));
		$this->redirect(array('action' => 'index'));
	}

/**
 * export csv method for rpm performance tables
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */

//public $components = array('RequestHandler', 'CsvView.CsvView');

	public function rpmperformance() 
	{
		$this->layout = 'ajax'; 
        $this->set('event', $this->Events->findById($id)); 

    $date2array = explode("-", $date2);
    $year = $date2array[0];
    $month = $date2array[1];
    $day = $date2array[2];

    $this->set(compact('date2')); 


   // $this->viewClass = 'CsvView.Csv';
   // $results = $this->Modelportfolio->find('all');
	//$excludePaths = array('Modelportfolio.PortfoliosID', 'Modelportfolio.FundsID', 'Modelportfolio.IndicesID', 'Modelportfolio.FundsList', 'Modelportfolio.IndexList', 'Modelportfolio.Date'); 
	//$customHeaders = array('Modelportfolio.PortfolioName' => 'RPM', 'Modelportfolio.OneMonthReturn' => '1 Month', 'Modelportfolio.ThreeMonthReturn' => '3 Months', 'Modelportfolio.SixMonthReturn' => '6 Months', 
	//	'Modelportfolio.OneYearReturn' => '1 Year', 'Modelportfolio.TwoYearReturn' => '2 Years', 'Modelportfolio.ThreeYearReturn' => '3 Years', 'Modelportfolio.FourYearReturn' => '4 Years', 'Modelportfolio.FiveYearReturn' => '5 Years', 
	//	'Modelportfolio.SinceInceptionReturn' => 'Since Inception (p.a)', 'Modelportfolio.SinceInceptionReturnC' => 'Since Inception (cum)');

	//$this->CsvView->quickExport($results, $excludePaths, $customHeaders);
}
}
